package com.fsm.demo.serviceImpl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.fsm.common.easyexcel.config.CellStyleModel;
import com.fsm.common.easyexcel.config.CustomCellStyleHandler;
import com.fsm.common.easyexcel.config.EasyExcelTitleHandler;
import com.fsm.common.easyexcel.dto.TSRoleVo;
import com.fsm.common.easyexcel.util.EasyExcelUtil;
import com.fsm.common.tools.Result.Result;
import com.fsm.demo.entity.excelbo.*;
import com.fsm.demo.service.EasyExcelService;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import lombok.Cleanup;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import java.io.*;
import java.net.URL;
import java.net.URLEncoder;
import java.util.*;

@Service
public class EasyExcelServiceImpl implements EasyExcelService {

    /**
     * 配置表格信息
     *
     * @param tsRoleVo
     * @return
     */
    @Override
    public Result configurationExcel(TSRoleVo tsRoleVo, HttpServletResponse response) throws Exception {
        List<TSRoleVo> data = new ArrayList<>();
        TSRoleVo tsRoleVo1 = new TSRoleVo();
        tsRoleVo1.setId("123");
        tsRoleVo1.setRoleName("角色1");
        tsRoleVo1.setRoleCode("001");
        tsRoleVo1.setCreateTime(new Date());

        TSRoleVo tsRoleVo2 = new TSRoleVo();
        tsRoleVo2.setId("321");
        tsRoleVo2.setRoleName("角色2");
        tsRoleVo2.setRoleCode("002");
        tsRoleVo2.setCreateTime(new Date());
        data.add(tsRoleVo1);
        data.add(tsRoleVo2);
        EasyExcelUtil<TSRoleVo> excelUtil = new EasyExcelUtil<>();

        List<CellStyleModel> cellStyleList = new ArrayList<>();
        //设置单元格字体,字体大小,字体颜色,加粗,斜体,下划线,上标,删除线
        cellStyleList.add(CellStyleModel.createFontCellStyleModel("客户信息表模版", 0, 0, "Arial", 20D, IndexedColors.YELLOW
                , false, false,null,null, false));
        //设置对齐方式   设置第二行右靠齐
        cellStyleList.add(CellStyleModel.createAlignmentCellStyleModel("客户信息表模版", 1, 0, HorizontalAlignment.RIGHT, VerticalAlignment.BOTTOM));
        //设置单元格背景颜色
        cellStyleList.add(CellStyleModel.createBackgroundColorCellStyleModel("客户信息表模版", 0, 0, IndexedColors.BLUE_GREY));
        //设置单元格边框类型和边框颜色
        cellStyleList.add(CellStyleModel.createBorderCellStyleModel("客户信息表模版", 0, 0, BorderStyle.DOUBLE, IndexedColors.RED));
        //设置自动换行
        cellStyleList.add(CellStyleModel.createWrapTextCellStyleModel("客户信息表模版", 0, 0, true));

        EasyExcelUtil.setResponseHeader(response,"客户信息表模版");
        try {
            excelUtil.writeExcelWithModel(response.getOutputStream(), data , TSRoleVo.class, "角色表模版" ,
                    new EasyExcelTitleHandler(null,null,null,null,"xxx") ,
                    new CustomCellStyleHandler(cellStyleList));
        } catch (IOException e) {
            e.printStackTrace();
        }
        return Result.ok();
    }

    /**
     * 最简单的写
     *
     * @param response
     * @return
     */
    @Override
    public Result demo1Excel(HttpServletResponse response) throws Exception {
        List<Demo1Bo> demo1Bos = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            Demo1Bo demo1Bo = Demo1Bo.builder()
                    .userId(i)
                    .userName("admin" + i)
                    .gender(i % 2 == 0 ? "男" : "女")
                    .salary(i * 1000.00)
                    .hireDate(new Date())
                    .build();
            demo1Bos.add(demo1Bo);
        }
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("信息表", "UTF-8").replaceAll("\\+", "%20");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        // 创建ExcelWriter对象
        ExcelWriter excelWriter = EasyExcel.write(outputStream, Demo1Bo.class).build();
        // 创建Sheet对象
        WriteSheet writeSheet = EasyExcel.writerSheet("信息").build();
        // 向Excel中写入数据
        excelWriter.write(demo1Bos, writeSheet);
        // 关闭流
        excelWriter.finish();
        return Result.ok();
    }

    /**
     * 排除/指定模型中的属性字段
     *
     * @param response
     * @return
     */
    @Override
    public Result demo2Excel(HttpServletResponse response) throws Exception {
        List<Demo2Bo> demo2Bos = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            Demo2Bo demo2Bo = Demo2Bo.builder()
                    .userId(i)
                    .userName("admin" + i)
                    .gender(i % 2 == 0 ? "男" : "女")
                    .salary(i * 1000.00)
                    .hireDate(new Date())
                    .build();
            demo2Bos.add(demo2Bo);
        }
        // 设置排除的属性 也可以在数据模型的字段上加@ExcelIgnore注解排除
        Set<String> excludeField = new HashSet<>();
        excludeField.add("hireDate");
        // 设置要导出的字段
        Set<String> includeFields = new HashSet<>();
        includeFields.add("userName");
        includeFields.add("gender");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("信息表", "UTF-8").replaceAll("\\+", "%20");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        EasyExcel.write(outputStream, Demo2Bo.class)
                .excludeColumnFiledNames(excludeField)
                .includeColumnFiledNames(includeFields)
                .sheet("信息")
                .doWrite(demo2Bos);
        return Result.ok();
    }

    /**
     * 插入指定的列
     *
     * @param response
     * @return
     */
    @Override
    public Result demo3Excel(HttpServletResponse response) throws Exception {
        List<Demo3Bo> demo3Bos = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            Demo3Bo demo3Bo = Demo3Bo.builder()
                    .userId(i)
                    .userName("admin" + i)
                    .gender(i % 2 == 0 ? "男" : "女")
                    .salary(i * 1000.00)
                    .hireDate(new Date())
                    .build();
            demo3Bos.add(demo3Bo);
        }
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("信息表", "UTF-8").replaceAll("\\+", "%20");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        EasyExcel.write(outputStream, Demo4Bo.class)
                .sheet("信息")
                .doWrite(demo3Bos);
        return Result.ok();
    }

    /**
     * 复杂头数据写入
     *
     * @param response
     * @return
     */
    @Override
    public Result demo4Excel(HttpServletResponse response) throws Exception {
        List<Demo4Bo> demo4Bos = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            Demo4Bo demo4Bo = Demo4Bo.builder()
                    .userId(i)
                    .userName("admin" + i)
                    .gender(i % 2 == 0 ? "男" : "女")
                    .salary(i * 1000.00)
                    .hireDate(new Date())
                    .build();
            demo4Bos.add(demo4Bo);
        }
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("信息表", "UTF-8").replaceAll("\\+", "%20");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        EasyExcel.write(outputStream, Demo4Bo.class)
                .sheet("信息")
                .doWrite(demo4Bos);
        return Result.ok();
    }

    /**
     * 写到Excel的Sheet中
     *
     * @param response
     * @return
     */
    @Override
    public Result demo5Excel(HttpServletResponse response) throws Exception {
        List<Demo4Bo> demo4Bos = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            Demo4Bo demo4Bo = Demo4Bo.builder()
                    .userId(i)
                    .userName("admin" + i)
                    .gender(i % 2 == 0 ? "男" : "女")
                    .salary(i * 1000.00)
                    .hireDate(new Date())
                    .build();
            demo4Bos.add(demo4Bo);
        }
        String fileName = URLEncoder.encode("信息表", "UTF-8").replaceAll("\\+", "%20");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        // 创建ExcelWriter对象
        ExcelWriter excelWriter = EasyExcel.write(outputStream, Demo4Bo.class).build();
        // 创建Sheet对象
        WriteSheet writeSheet1 = EasyExcel.writerSheet("信息").build();
        // 向Excel的同一个Sheet重复写入数据
        for (int i = 0; i < 2; i++) {
            excelWriter.write(demo4Bos, writeSheet1);
        }
        // 向Excel的同一个Sheet重复写入数据
        for (int i = 1; i < 3; i++) {
            // 创建Sheet对象
            WriteSheet writeSheet2 = EasyExcel.writerSheet("信息" + i).build();
            excelWriter.write(demo4Bos, writeSheet2);
        }
        // 关闭流
        excelWriter.finish();
        return Result.ok();
    }

    /**
     * 日期/数字类型格式化
     *
     * @param response
     * @return
     */
    @Override
    public Result demo6Excel(HttpServletResponse response) throws Exception {
        List<Demo5Bo> demo5Bos = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            Demo5Bo demo5Bo = Demo5Bo.builder()
                    .userId(i)
                    .userName("admin" + i)
                    .gender(i % 2 == 0 ? "男" : "女")
                    .salary(i * 1000.00)
                    .hireDate(new Date())
                    .build();
            demo5Bos.add(demo5Bo);
        }
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("信息表", "UTF-8").replaceAll("\\+", "%20");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        EasyExcel.write(outputStream, Demo5Bo.class)
                .sheet("信息")
                .doWrite(demo5Bos);
        return Result.ok();
    }

    /**
     * 写入图片到Excel
     *
     * @param response
     * @return
     */
    @Override
    public Result demo7Excel(HttpServletResponse response) throws Exception {
        // 图片位置
        String imagePath = "C:\\Users\\78789\\Desktop\\图片\\111.jpg";
        // 网络图片
        URL url = new URL("https://cn.bing.com/th?id=OHR.TanzaniaBeeEater_ZH-CN3246625733_1920x1080.jpg&rf=LaDigue_1920x1080.jpg&pid=hp");
        // 将图片读取到二进制数据中
        byte[] bytes = new byte[(int) new File(imagePath).length()];
        InputStream inputStream = new FileInputStream(imagePath);
        inputStream.read(bytes, 0, bytes.length);

        List<Demo6Bo> imageDataList = new ArrayList<>();

        // 创建数据模板
        Demo6Bo imageData = Demo6Bo.builder()
                .file(new File(imagePath))
                .inputStream(new FileInputStream(imagePath))
                .str(imagePath)
                .byteArr(bytes)
                .url(url)
                .build();
        // 添加要写入的图片模型
        imageDataList.add(imageData);

        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("信息表", "UTF-8").replaceAll("\\+", "%20");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        // 写数据
        EasyExcel.write(outputStream, Demo6Bo.class)
                .sheet("信息")
                .doWrite(imageDataList);
        return Result.ok();
    }

    /**
     * 通过注解形式设置写入Excel样式
     *
     * @param response
     * @return
     */
    @Override
    public Result demo8Excel(HttpServletResponse response) throws Exception {
        List<Demo7Bo> demo7Bos = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            Demo7Bo demo7Bo = Demo7Bo.builder()
                    .string("字符串")
                    .date(new Date())
                    .doubleData(888.88)
                    .build();
            demo7Bos.add(demo7Bo);
        }
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("信息表", "UTF-8").replaceAll("\\+", "%20");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        EasyExcel.write(outputStream, Demo7Bo.class)
                .sheet("信息")
                .doWrite(demo7Bos);
        return Result.ok();
    }

    /**
     * 合并单元格
     *
     * @param response
     * @return
     */
    @Override
    public Result demo9Excel(HttpServletResponse response) throws Exception {
        List<Demo8Bo> demo8Bos = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            Demo8Bo demo8Bo = Demo8Bo.builder()
                    .userId(i)
                    .userName("admin" + i)
                    .gender(i % 2 == 0 ? "男" : "女")
                    .salary(i * 1000.00)
                    .hireDate(new Date())
                    .build();
            demo8Bos.add(demo8Bo);
        }
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("信息表", "UTF-8").replaceAll("\\+", "%20");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        EasyExcel.write(outputStream, Demo8Bo.class)
                .sheet("信息")
                .doWrite(demo8Bos);
        return Result.ok();
    }

    /**
     * 自定义转换器
     *
     * @param response
     * @return
     */
    @Override
    public Result demo10Excel(HttpServletResponse response) throws Exception {
        List<Demo9Bo> demo9Bos = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            Demo9Bo demo9Bo = Demo9Bo.builder()
                    .userId(i)
                    .userName("admin" + i)
                    .gender(i % 2)
                    .salary(i * 1000.00)
                    .hireDate(new Date())
                    .build();
            demo9Bos.add(demo9Bo);
        }
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("信息表", "UTF-8").replaceAll("\\+", "%20");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=" + fileName + ".xlsx");
        OutputStream outputStream = response.getOutputStream();
        EasyExcel.write(outputStream, Demo9Bo.class)
                .sheet("信息")
                .doWrite(demo9Bos);
        return Result.ok();
    }

    /**
     * 导入转换成List<List<String>>
     *
     * @param request
     * @return
     */
    @Override
    public Result demo11Excel(HttpServletRequest request) throws Exception {
        //获取文件
        MultipartFile file = ((MultipartHttpServletRequest)request).getFile("file");
        @Cleanup
        InputStream inputStream = file.getInputStream();
        List<List<String>> lists = EasyExcelUtil.parseFromExcel(inputStream);
        for(List<String> data : lists){
            System.out.println(data);
        }
        return Result.ok(lists);
    }

}
